
* TABLE 6: GAINS AND LOSSES IN REVENUE FROM THE 2006 AND 2008 REFORMS 

// NOTE: data needed for this do-file cannot be shared due to privacy reasons
// NOTE: the output of this table is an excel-file. It needs to formatted and exported to latex manually


version 16.1

clear all
cap log close 
cap clear matrix
set more off



use "/Volumes/OW_data/my_dta_v8.1.dta", clear

// get the hypothetical tax rates
forval n=1/2 {
drop einfachSteuer_satzbest_h`n' einfachSteuer_h`n'  einfachSteuer_verm_durchschn_h`n'  steuern_einkK_h`n' steuern_einkG_h`n' steuern_einkKi_h`n' steuern_vermK_h`n' steuern_vermG_h`n' steuern_vermKi_h`n' steuern_eink_h`n' steuern_verm_h`n' avgt_stek_gross_h`n'  avgt_stekK_gross_h`n'  avgt_stekG_gross_h`n' avgt_stekKi_gross_h`n' avgt_stek_taxable_h`n'   avgt_stekK_taxable_h`n'  avgt_stekG_taxable_h`n'  avgt_stekKi_taxable_h`n'   avgt_stverm_net_h`n'   avgt_stvermK_net_h`n'  avgt_stvermG_net_h`n'  avgt_stvermKi_net_h`n'   avgt_stverm_taxable_h`n'   avgt_stvermK_taxable_h`n'  avgt_stvermG_taxable_h`n'  avgt_stvermKi_taxable_h`n' 
}
drop einfachSteuer_durchschn_h1 einfachSteuer_verm_satzb_h1 einfachSteuer_h1_verm einfachSteuer_verm_h2 steuern_einkKGKi_h2 steuern_einkKGKi_h1
cap drop einfachSteuer_verm_satzbest_h2
cap drop einfSt_durcschnittssatz_h2
cap drop einfachSteuer_durchschn_h2

do "$mypathRR/Resources/calc_taxrates_hypothetical.do"

label var zuzugjahre "move-in year (including several moves)"
label var zuzugjahr "move-in year (last move)"
drop if year>2010
drop if zuzugjahre>2010

* Adjust taxable income to account for the changes in deduction in 2008
// to account for the increased deduction under the flat-tax regime
replace esteuerbst=esteuerbst+10000 if year>=2008 


* GENERATE THE TREATMENT DEFINITION BASED ON TAXABLE INCOME
gen taxinc_thre = (esteuerbst>= 300000 & esteuerbst <.)
label var taxinc_thre "Taxable income threshold regressive tax"

gen taxwea_thre = (vsteuerbst>=5000000 & vsteuerbst <.)
label var taxwea_thre "Taxable wealth threshold regressive tax"


* GENERATE AN ALTERNATIVE TREATMENT DEFINITION BASED ON RATE-DETERMINING INCOME
gen taxinc_thre2 = (esatzbestkopf>= 300000 & esatzbestkopf <.)
label var taxinc_thre2 "Taxable income threshold regressive tax"

gen taxwea_thre2 = (vsatzbestkopf>=5000000 & vsatzbestkopf <.)
label var taxwea_thre2 "Taxable wealth threshold regressive tax"

* DEFLATE THE INCOME VARIABLES
cap drop _merge
merge m:1 year using "$mypathRR/Datasets/CPI.dta"

drop _merge
drop if year==.


foreach var in esteuerbst vsteuerbst  steuern_einkKGKi steuern_einkKGKi_h  steuern_verm  steuern_verm_h z199s1 z450s1 z300s1 z470s1 inmob_inc mob_inc {
replace `var'=`var'/cpi*100.5 //deflate the series
local u : variable label `var'
local l= "`u' (real)" 
label var `var'"`l'"
}

drop if year > 2010
drop if zuzugjahre > 2010





* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *  
* REVENUE LOSSES ARISING FROM TAX CUTS ON ALL RESIDENTS LIVING IN OW ALREADY BEFORE 2006
*-----------------------------------------------------------
cd "$mypathRR/Results"

gen revenue = (steuern_verm + steuern_einkKGKi)
gen revenue_h = (steuern_verm_h + steuern_einkKGKi_h)
gen loss = revenue - revenue_h
bys year: sum loss

gen loss_inc = (steuern_einkKGKi - steuern_einkKGKi_h)
gen loss_wea = (steuern_verm - steuern_verm_h)

*-----------------------------------------------------------
// ALL TAXPAYERS
preserve
sort persid _spell zuzugjahre
by persid _spell (zuzugjahre): gen keeper=(zuzugjahre<2006)
keep if keeper ==1 // keep only those who moved in before 2006

sort persid year

* collapse
collapse (count) persid (sum) revenue revenue_h loss loss_inc loss_wea , by(year)
foreach var in revenue revenue_h loss loss_inc loss_wea {
	replace `var' = `var'/1000000
}
	
* Save data on total losses * 
label var loss "Total tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var loss_inc  "Income tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var loss_wea  "Wealth tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var revenue "Estimated total tax revenue from residents (moving-date<2006) (in mio CHF)"
label var revenue_h "Hypothetical total tax revenue from residents (moving-date<2006) w/o reform (in mio CHF)"
foreach var in loss loss_inc loss_wea {
	rename `var' `var'_all
}
rename persid N_all_losses

save "tot_loss_residents-all.dta", replace
restore

*-----------------------------------------------------------
// NON-RICH
preserve
sort persid _spell zuzugjahre
by persid _spell (zuzugjahre): gen keeper=(zuzugjahre<2006) & (taxwea_thre2==0 & taxinc_thre2==0) // keep only the non-rich
keep if keeper == 1 // keep only those who moved in before 2006

sort persid year

* collapse
collapse (count) persid (sum) revenue revenue_h loss loss_inc loss_wea , by(year)
foreach var in revenue revenue_h loss loss_inc loss_wea {
	replace `var' = `var'/1000000
}

cap drop if year>2011
cap drop if zuzugjahre>2011

* Save data on total losses * 
label var loss "Total tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var loss_inc  "Income tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var loss_wea  "Wealth tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var revenue "Estimated total tax revenue from residents (moving-date<2006) (in mio CHF)"
label var revenue_h "Hypothetical total tax revenue from residents (moving-date<2006) w/o reform (in mio CHF)"
foreach var in loss loss_inc loss_wea {
	rename `var' `var'_nonrich
}
rename persid N_nonrich_losses

save "tot_loss_residents-non_rich.dta", replace
restore

*-----------------------------------------------------------
// RICH
preserve
sort persid _spell zuzugjahre
by persid _spell (zuzugjahre): gen keeper=(zuzugjahre<2006) & (taxwea_thre2==1 | taxinc_thre2==1)
keep if keeper == 1 // keepS only the rich

sort persid year

* collapse
collapse (count) persid (sum) revenue revenue_h loss loss_inc loss_wea , by(year)
foreach var in revenue revenue_h loss loss_inc loss_wea {
	replace `var' = `var'/1000000
}

cap drop if year>2011
cap drop if zuzugjahre>2011

* Save data on total losses * 
label var loss "Total tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var loss_inc  "Income tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var loss_wea  "Wealth tax revenue loss from residents (moving-date<2006) (in mio CHF)"
label var revenue "Estimated total tax revenue from residents (moving-date<2006) (in mio CHF)"
label var revenue_h "Hypothetical total tax revenue from residents (moving-date<2006) w/o reform (in mio CHF)"
foreach var in loss loss_inc loss_wea {
	rename `var' `var'_rich
}
rename persid N_rich_losses

save "tot_loss_residents-rich.dta", replace
restore




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *  
* REVENUE GAINS ARISING FROM NEW RICH TAXPAYERS MOVING TO OW IN 2006 & AFTERWARDS
gen gain = revenue
bys year: sum gain

gen gain_inc = steuern_einkKGKi
gen gain_wea = steuern_verm

*-----------------------------------------------------------
// ALL TAXPAYERS
preserve
sort persid _spell zuzugjahre
by persid _spell (zuzugjahre): gen keeper=(zuzugjahre>2005 & zuzugkt!=6 & firstyear_raw>2005)
keep if keeper ==1 // keep only those who moved in before 2006

sort persid year

* collapse
collapse (count) persid (sum) revenue revenue_h gain gain_inc gain_wea , by(year)
foreach var in revenue revenue_h gain gain_inc gain_wea {
	replace `var' = `var'/1000000
}

* Save data on total gains * 
label var gain "Total tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var gain_inc  "Income tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var gain_wea  "Wealth tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var revenue "Estimated total tax revenue from inmovers (moving-date>2005) (in mio CHF)"
label var revenue_h "Hypothetical total tax revenue from inmovers (moving-date>2005) w/o reform (in mio CHF)"
foreach var in gain gain_inc gain_wea revenue revenue_h {
	rename `var' `var'_all
}
rename persid N_all_gains

save "tot_gain_inmovers-all.dta", replace
restore

*-----------------------------------------------------------
// NON-RICH
preserve
sort persid _spell zuzugjahre
by persid _spell (zuzugjahre): gen keeper=(zuzugjahre>2005 & zuzugkt!=6 & firstyear_raw>2005) & (taxwea_thre2==0 & taxinc_thre2==0) // keep only the non-rich
keep if keeper ==1 // keep only those who moved in before 2006

sort persid year

* collapse
collapse (count) persid (sum) revenue revenue_h gain gain_inc gain_wea , by(year)
foreach var in revenue revenue_h gain gain_inc gain_wea {
	replace `var' = `var'/1000000
}

cap drop if year>2011
cap drop if zuzugjahre>2011

* Save data on total gains * 
label var gain "Total tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var gain_inc  "Income tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var gain_wea  "Wealth tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var revenue "Estimated total tax revenue from inmovers (moving-date>2005) (in mio CHF)"
label var revenue_h "Hypothetical total tax revenue from inmovers (moving-date>2005) w/o reform (in mio CHF)"
foreach var in gain gain_inc gain_wea revenue revenue_h {
	rename `var' `var'_nonrich
}
rename persid N_nonrich_gains

save "tot_gain_inmovers-non_rich.dta", replace
restore

*-----------------------------------------------------------
// RICH
preserve
sort persid _spell zuzugjahre
by persid _spell (zuzugjahre): gen keeper=(zuzugjahre>2005 & zuzugkt!=6 & firstyear_raw>2005) & (taxwea_thre2==1 | taxinc_thre2==1)
keep if keeper == 1 // keepS only the rich

sort persid year

* collapse
collapse (count) persid (sum) revenue revenue_h gain gain_inc gain_wea , by(year)
foreach var in revenue revenue_h gain gain_inc gain_wea {
	replace `var' = `var'/1000000
}

cap drop if year>2011
cap drop if zuzugjahre>2011

* Save data on total gains * 
label var gain "Total tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var gain_inc  "Income tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var gain_wea  "Wealth tax revenue gain from inmovers (moving-date>2005) (in mio CHF)"
label var revenue "Estimated total tax revenue from inmovers (moving-date>2005) (in mio CHF)"
label var revenue_h "Hypothetical total tax revenue from inmovers (moving-date>2005) w/o reform (in mio CHF)"

foreach var in gain gain_inc gain_wea revenue revenue_h {
	rename `var' `var'_rich
}
rename persid N_rich_gains

save "tot_gain_inmovers-rich.dta", replace
restore



* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 

* * * * * * * * * * * * * * * * * * * * * * * 
* SHOW GAINS AND LOSSES FROM THE RICH *
* * * * * * * * * * * * * * * * * * * * * * * 
use "tot_loss_residents-all.dta", clear
merge 1:m year using "tot_loss_residents-non_rich.dta", nogen
merge 1:m year using "tot_loss_residents-rich.dta", nogen
merge 1:m year using "tot_gain_inmovers-all.dta", nogen
merge 1:m year using "tot_gain_inmovers-non_rich.dta", nogen
merge 1:m year using "tot_gain_inmovers-rich.dta", nogen
drop revenue*

rm "tot_loss_residents-all.dta"
rm "tot_loss_residents-non_rich.dta"
rm "tot_loss_residents-rich.dta"
rm "tot_gain_inmovers-all.dta"
rm "tot_gain_inmovers-non_rich.dta"
rm "tot_gain_inmovers-rich.dta"

foreach var in N_all_gains gain_all gain_inc_all gain_wea_all N_nonrich_gains gain_nonrich gain_inc_nonrich gain_wea_nonrich N_rich_gains gain_rich gain_inc_rich gain_wea_rich {
replace `var'=0 if `var'==.
}

foreach var in _all _nonrich _rich _inc_all _inc_nonrich _inc_rich _wea_all _wea_nonrich _wea_rich {
	gen net`var' = loss`var' + gain`var'
}

order year loss_all gain_all net_all loss_inc_all gain_inc_all net_inc_all loss_wea_all gain_wea_all net_wea_all ///
loss_nonrich gain_nonrich net_nonrich loss_inc_nonrich gain_inc_nonrich net_inc_nonrich loss_wea_nonrich gain_wea_nonrich net_wea_nonrich ///
loss_rich gain_rich net_rich loss_inc_rich gain_inc_rich net_inc_rich loss_wea_rich gain_wea_rich net_wea_rich ///
N_all_losses N_all_gains N_nonrich_losses N_nonrich_gains N_rich_losses N_rich_gains
/*
Der Kanton leistet zur Minderung der Steuerausfälle der Gemeinden in den Jahren 2006 bis 2010 folgende jährliche Beiträge:
a. 2006:6,3MillionenFranken,
b. 2007:5,25MillionenFranken,
c. 2008: 4,7 Millionen Franken,
d. 2009:4,15MillionenFranken,
e. 2010:3,1MillionenFranken.
*/

gen ausgleich = 0
replace ausgleich = 6.30 if year==2006
replace ausgleich = 5.25 if year==2007
replace ausgleich = 4.70 if year==2008
replace ausgleich = 4.15 if year==2009
replace ausgleich = 3.10 if year==2010


gen net_effect = net_all + ausgleich 
label var net_effect "Net effect including SNB payments to cover initial losses"


 * * * * * * * * * * * *   SAVE TABLE TO EXCEL   * * * * * * * * * * *
keep if year>2005

order 	year ///
		loss_rich	  gain_rich		net_rich	 net_inc_rich	  net_wea_rich	 	///
		loss_nonrich  gain_nonrich	net_nonrich	 net_inc_nonrich  net_wea_nonrich	///
		loss_all	  gain_all		net_all		 net_inc_all	  net_wea_all		///

keep 	year ///
		loss_rich	  gain_rich		net_rich	 net_inc_rich	  net_wea_rich	 	///
		loss_nonrich  gain_nonrich	net_nonrich	 net_inc_nonrich  net_wea_nonrich	///
		loss_all	  gain_all		net_all		 net_inc_all	  net_wea_all		///



export excel year loss_rich gain_rich net_rich net_inc_rich net_wea_rich 		///
		using "Tab6-results.xlsx", 	///
		replace firstrow(variables) sheet("Panel A")
		
		
export excel year loss_nonrich gain_nonrich net_nonrich net_inc_nonrich net_wea_nonrich 	///
		using "Tab6-results.xlsx", 	///
		 firstrow(variables) sheet("Panel B")
				
export excel year loss_all gain_all net_all net_inc_all net_wea_all				///
		using "Tab6-results.xlsx", 	///
		 firstrow(variables) sheet("Panel C")	
		

						* * * * *  E N D  * * * * * * 
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

